#!/bin/bash
# 作用：DWS层（1日汇总），首日数据装载（DWD -> DWS_1d）

if [ $# -ne 1 ]
then
	echo "请输入正确参数表名/all" && exit
fi

dws_interaction_sku_favor_add_1d="set hive.exec.dynamic.partition.mode=nonstrict;
with
fa as (
    select dt,sku_id,
           count(1) favor_add_count_1d
    from dwd_interaction_favor_add_inc
    where dt<='2023-01-14'
    group by dt,sku_id
),si as (
    select id,
           price,
           sku_name,
           sku_desc,
           weight,
           is_sale,
           spu_id,
           spu_name,
           category3_id,
           category3_name,
           category2_id,
           category2_name,
           category1_id,
           category1_name,
           tm_id,
           tm_name
    from dim_sku_full
    where dt='2023-01-14'
)
insert overwrite table dws_interaction_sku_favor_add_1d partition (dt)
select fa.sku_id,
       si.sku_name,
       si.category1_id,
       si.category1_name,
       si.category2_id,
       si.category2_name,
       si.category3_id,
       si.category3_name,
       si.tm_id,
       si.tm_name,
       fa.favor_add_count_1d,
       dt
from fa
join si on fa.sku_id=si.id;"
dws_tool_user_coupon_coupon_used_1d="set hive.exec.dynamic.partition.mode=nonstrict;
with
cu as (
    select dt,user_id,coupon_id,
           count(1) used_count_1d
    from dwd_tool_coupon_used_inc
    where dt<='2023-01-14'
    group by dt,user_id,coupon_id
),ci as (
    select id,coupon_name,coupon_type_code,coupon_type_name,benefit_rule
    from dim_coupon_full
    where dt='2023-01-14'
)
insert overwrite table dws_tool_user_coupon_coupon_used_1d partition (dt)
select cu.user_id,
       cu.coupon_id,
       ci.coupon_name,
       ci.coupon_type_code,
       ci.coupon_type_name,
       ci.benefit_rule,
       cu.used_count_1d,
       dt
from cu
join ci on cu.coupon_id=ci.id;"
dws_trade_province_order_1d="set hive.exec.dynamic.partition.mode=nonstrict;
with
od as (
    select dt,user_id,province_id,
           count(distinct order_id) order_count_1d,
           sum(split_original_amount) order_original_amount_1d,
           nvl(sum(split_activity_amount),0.0) activity_reduce_amount_1d,
           nvl(sum(split_coupon_amount),0.0)  coupon_reduce_amount_1d,
           sum(split_total_amount) order_total_amount_1d
    from dwd_trade_order_detail_inc
    where dt<='2023-01-14'
    group by dt,user_id,province_id
),pr as (
    select id,province_name,area_code,iso_code,iso_3166_2
    from dim_province_full
    where dt='2023-01-14'
)
insert overwrite table dws_trade_province_order_1d partition (dt)
select od.province_id,
       pr.province_name,
       pr.area_code,
       pr.iso_code,
       pr.iso_3166_2,
       od.order_count_1d,
       od.order_original_amount_1d,
       od.activity_reduce_amount_1d,
       od.coupon_reduce_amount_1d,
       od.order_total_amount_1d,
       dt
from od
join pr on od.province_id=pr.id;"
dws_trade_user_cart_add_1d="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_user_cart_add_1d partition (dt)
select user_id,
       count(1),
       sum(sku_num),
       dt
from dwd_trade_cart_add_inc
where dt<='2023-01-14'
group by dt,user_id;"
dws_trade_user_order_1d="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_user_order_1d partition (dt)
select user_id,
       count(distinct order_id),
       sum(sku_num),
       sum(split_original_amount),
       nvl(sum(split_activity_amount),0.0),
       nvl(sum(split_coupon_amount),0.0),
       sum(split_total_amount),
       dt
from dwd_trade_order_detail_inc
where dt<='2023-01-14'
group by dt,user_id;"
dws_trade_user_payment_1d="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_user_payment_1d partition (dt)
select user_id,
       count(1),
       sum(sku_num),
       sum(split_payment_amount),
       dt
from dwd_trade_pay_detail_suc_inc
where dt<='2023-01-14'
group by dt,user_id;"
dws_trade_user_sku_order_1d="set hive.exec.dynamic.partition.mode=nonstrict;
with
od as (
    select dt,user_id,sku_id,
           count(distinct order_id) order_count_1d,
           sum(sku_num) order_num_1d,
           sum(split_original_amount) order_original_amount_1d,
           sum(split_activity_amount) activity_reduce_amount_1d,
           sum(split_coupon_amount) coupon_reduce_amount_1d,
           sum(split_total_amount) order_total_amount_1d
    from dwd_trade_order_detail_inc
    where dt<='2023-01-14'
    group by dt,user_id,sku_id
),si as (
    select id,
           price,
           sku_name,
           sku_desc,
           weight,
           is_sale,
           spu_id,
           spu_name,
           category3_id,
           category3_name,
           category2_id,
           category2_name,
           category1_id,
           category1_name,
           tm_id,
           tm_name
    from dim_sku_full
    where dt='2023-01-14'
)
insert overwrite table dws_trade_user_sku_order_1d partition (dt)
select od.user_id,
       od.sku_id,
       si.sku_name,
       si.category1_id,
       si.category1_name,
       si.category2_id,
       si.category2_name,
       si.category3_id,
       si.category3_name,
       si.tm_id,
       si.tm_name,
       od.order_count_1d,
       od.order_num_1d,
       od.order_original_amount_1d,
       nvl(od.activity_reduce_amount_1d,0.0),
       nvl(od.coupon_reduce_amount_1d,0.0),
       od.order_total_amount_1d,
       od.dt
from od
join si on od.sku_id=si.id;"
dws_traffic_page_visitor_page_view_1d="insert overwrite table dws_traffic_page_visitor_page_view_1d partition (dt='2023-01-14')
select mid_id,brand,model,operate_system,page_id,
       sum(during_time),
       count(1)
from dwd_traffic_page_view_inc
where dt='2023-01-14'
group by mid_id,brand,model,operate_system,page_id;"
dws_traffic_session_page_view_1d="insert overwrite table dws_traffic_session_page_view_1d partition (dt='2023-01-14')
select session_id,
       mid_id,
       brand,
       model,
       operate_system,
       version_code,
       channel,
       sum(during_time),
       count(1)
from dwd_traffic_page_view_inc
where dt='2023-01-14'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;"

case $1 in
"all")
	$HIVE_HOME/bin/hive -e "${dws_interaction_sku_favor_add_1d}${dws_tool_user_coupon_coupon_used_1d}${dws_trade_province_order_1d}${dws_trade_user_cart_add_1d}${dws_trade_user_order_1d}${dws_trade_user_payment_1d}${dws_trade_user_sku_order_1d}${dws_traffic_page_visitor_page_view_1d}${dws_traffic_session_page_view_1d}"
;;
"dws_interaction_sku_favor_add_1d")
    $HIVE_HOME/bin/hive -e "${dws_interaction_sku_favor_add_1d}"
;;
"dws_tool_user_coupon_coupon_used_1d")
    $HIVE_HOME/bin/hive -e "${dws_tool_user_coupon_coupon_used_1}"
;;
"dws_trade_province_order_1d")
    $HIVE_HOME/bin/hive -e "${dws_trade_province_order_1d}"
;;
"dws_trade_user_cart_add_1d")
    $HIVE_HOME/bin/hive -e "${dws_trade_user_cart_add_1d}"
;;
"dws_trade_user_order_1d")
    $HIVE_HOME/bin/hive -e "${dws_trade_user_order_1d}"
;;
"dws_trade_user_payment_1d")
    $HIVE_HOME/bin/hive -e "${dws_trade_user_payment_1d}"
;;
"dws_trade_user_sku_order_1d")
    $HIVE_HOME/bin/hive -e "${dws_trade_user_sku_order_1d}"
;;
"dws_traffic_page_visitor_page_view_1d")
    $HIVE_HOME/bin/hive -e "${dws_traffic_page_visitor_page_view}"
;;
"dws_traffic_session_page_view_1d")
    $HIVE_HOME/bin/hive -e "${dws_traffic_session_page_view_1d}"
;;
esac
